use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};
use sqlx::{Error, FromRow, Pool, Postgres};
use uuid::Uuid;

#[derive(Debug, Clone, Serialize, Deserialize, FromRow, Default)]
pub struct SysUserGuild {
    pub user_id: Option<Uuid>,
    pub guild_id: Option<Uuid>,
    pub nick: Option<String>,
    pub avatar: Option<String>,
    pub joined_at: Option<NaiveDateTime>,
}

impl SysUserGuild {
    pub async fn select_guild_name(pool: &Pool<Postgres>, guild_name: &str) -> Result<Vec<Self>, Error> {
        let sql = "SELECT a.* FROM sys_user_guild a, sys_user b, sys_guild c WHERE a.user_id = b.id AND a.guild_id = c.id AND c.name = $1";
        sqlx::query_as::<_, Self>(sql)
            .bind(guild_name)
            .fetch_all(pool).await
    }

    pub async fn select_user_guild_name(pool: &Pool<Postgres>, user_name: &str, guild_name: &str) -> Result<Self, Error> {
        let sql = "SELECT a.* FROM sys_user_guild a, sys_user b, sys_guild c WHERE a.user_id = b.id AND a.guild_id = c.id AND b.name = $1 AND c.name = $2";
        sqlx::query_as::<_, Self>(sql)
            .bind(user_name)
            .bind(guild_name)
            .fetch_one(pool).await
    }

    pub fn get_joined_at_timestamp(&self) -> Option<i64> {
        if let Some(j) = self.joined_at {
            Option::from(j.and_utc().timestamp())
        } else { None }
    }
}